import xlwt

from quote_folder import Db_connect

db = Db_connect.DB_connect().online_big_data()
cursor = db.cursor()

info_dic = []

with open("sqh.txt", "r", encoding="utf-8") as fr:
    for sqh in fr:
        application_num = "ZL" + sqh.strip()
        print(application_num)
        sql = f"SELECT case_status, sq_date, patent_user, agency, application_number, fm_name, SUBSTRING_INDEX(fmr,',', 1)  FROM xhhg_pantent_status WHERE application_number = '{application_num}'"
        cursor.execute(sql)
        data = cursor.fetchall()
        try:
            if data[0][4][6] == '1':
                pty = "发明专利"
            elif data[0][4][6] == '2':
                pty = "实用新型专利"
            else:
                pty = '外观设计专利'
            item = {
                "sqh": data[0][4],
                "case_status": data[0][0],
                "sq_date": data[0][1],
                "patent_user": data[0][2],
                "agency": data[0][3],
                'name': data[0][5],
                'fmr': data[0][6],
                "pty": pty
            }
        except:
            item = {
                "sqh": "",
                "case_status": "",
                "sq_date": "",
                "patent_user": "",
                "agency": '',
                'name': '',
                'fmr': '',
                "pty": '',
            }
        print(item)
        info_dic.append(item)

workbook = xlwt.Workbook()
sheet = workbook.add_sheet("费用详情")
sheet.write(0, 0, '序号')
sheet.write(0, 1, 'zhuangtai ')
sheet.write(0, 2, '申请日')
sheet.write(0, 3, '专利权人')
sheet.write(0, 4, "代理机构")
sheet.write(0, 5, "sqh")
sheet.write(0, 6, '专利名称')
sheet.write(0, 7, 'fmr')
sheet.write(0, 8, 'pty')
for i in range(len(info_dic)):
    sheet.write(i + 1, 0, i + 1)
    sheet.write(i + 1, 1, info_dic[i]['case_status'])
    sheet.write(i + 1, 2, info_dic[i]['sq_date'])
    sheet.write(i + 1, 3, info_dic[i]['patent_user'])
    sheet.write(i + 1, 4, info_dic[i]['agency'])
    sheet.write(i + 1, 5, info_dic[i]['sqh'])
    sheet.write(i + 1, 6, info_dic[i]['name'])
    sheet.write(i + 1, 7, info_dic[i]['fmr'])
    sheet.write(i + 1, 8, info_dic[i]['pty'])

workbook.save('ASAS.xls')
